package sqlToGUI.testing.sendingInput;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import java.util.GregorianCalendar;
import java.util.Properties;


public class DatabasePrinter extends Thread{

	/**Property file holding info about how to create the database */
	private Properties databaseInfo;
	/**Property file holding info about the tables in the database
	 * to be read from */
	private Properties tables;

	/**variable holding the conenction to the database */
	private Connection con;
	/**The url to connect to the database */
	private String url;

	/**The current table being read from the database */
	private String currentTablePrinted;
	
	/**The string used for executing all the queries for reading data */
	private String query;
	/**Variable for collecting returned 'select type' queries that will
	 * contain the buttom row of the entire database in string format */
	private StringBuffer databaseRowPrintOut;
	
	/**Statement for executing search queries in a database */
	private Statement statement;
	/**Set containing the returned values from an executed sql statement */
	private ResultSet rs;
	
	
	/**Class constructor */
	public DatabasePrinter(){
		statement = initializeDatabase();
	}

	/**
	 * Register database driver, establish connection, and return the 
	 * corresponding statement object. @return a statement corresponding to 
	 * the connection. Null if the connection failed.
	 */
	private Statement initializeDatabase(){
		try{
			/**Initialize properties */
			databaseInfo = new Properties();
			databaseInfo.load(new FileInputStream
					("properties/databaseInfo.properties"));
			tables = new Properties();
			tables.load(new FileInputStream
					("properties/tables.properties"));

		}catch (FileNotFoundException e){
			e.printStackTrace();
			System.err.print("\nDatabase printer: Properties file for reading database + " +
			"not found\n");
		}catch (IOException e){
			e.printStackTrace();
			System.err.print("\nDatabase printer: Properties file for reading database + " +
			"not found\n");
		}

		try {
			/**Register the driver class */
			Class.forName(databaseInfo.getProperty("DatabaseDriverClass"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.err.println("Database printer: You need to install the specified database " +
			"driver before running this test.");
			System.exit(1);
		}

		/**For example, url looks like "jdbc:mysql://localhost:3306/mysql" */

		try {
			/**Try to connect to the database using the provided information 
			 * With the schema set to what is provided. Let's say it's 
			 * "bluesky" */
			url = "jdbc:mysql://"+databaseInfo.getProperty("DatabaseAddress")
			+":"+databaseInfo.getProperty("DatabasePort")+"/"+
			databaseInfo.getProperty("Schema");
			con = (Connection) DriverManager.getConnection(url,
					databaseInfo.getProperty("User"), 
					databaseInfo.getProperty("Password"));
		} catch (SQLException e) {
			
			/**If the connection failed, than try to connect without a default 
			 * schema */
			System.out.println("Database printer: Database: " + 
					databaseInfo.getProperty("Schema") + 
			" does not exist.\nCreating new scheme...");
			url = "jdbc:mysql://"+databaseInfo.getProperty("DatabaseAddress")
			+":"+databaseInfo.getProperty("DatabasePort");
			
			try{
				con = (Connection) DriverManager.getConnection(url,
						databaseInfo.getProperty("User"), 
						databaseInfo.getProperty("Password"));
			}catch (SQLException d){
				/**Now it really goes wrong. Exit the system */
				d.printStackTrace();
				return null;
			}
		}
		
		System.out.println("Database printer: Database Connection for reading "
				+ "data established.");
		System.out.println("URL: " + url);
		System.out.println("Connection: " + con);
		
		/**Try to initialize the statement */
		try {
			return (Statement) con.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}	
	}

	/**Start printing the data form the database */
	@SuppressWarnings("deprecation")
	public void run(){

		System.out.print("\n");
		while(true){
			
			/**Wait half a second before printing each row */
			try {
				Thread.sleep(100);
			} catch (InterruptedException e1) {
				e1.printStackTrace();
			}
			
			databaseRowPrintOut = new StringBuffer();
			databaseRowPrintOut.append("|");
			
			/**Iterate through each table in the database */
			for(int i=0; i < Integer.
			parseInt(tables.getProperty("NumberOfTables")); i++){

				/**Use the timer for accessing the buttom rows with
				 * the most recent timestamp - set this value back a few 
				 * seconds to avoid returning null values for results
				 * that have not yet entered the database */
				GregorianCalendar timeNow = new GregorianCalendar();
				Timestamp ts =  new Timestamp(timeNow.getTimeInMillis());
				ts.setSeconds(ts.getSeconds()-4);
				
				/**Create the specific SQL query for each table */
				currentTablePrinted = tables.getProperty("Table" + (i+1));				
				query = "SELECT * FROM " + currentTablePrinted + " A1 " + " " +
				        "WHERE A1.Timestamp IN " +
					       "(SELECT cast(timestamp as char) timestamp " +
						   "FROM " + currentTablePrinted + " " +
 						   "WHERE timestamp > '" 
						   + ts.toString().substring(0, 19) + "');";
				
				/**Select the buttom row from each table, and append it to the
				 * rest of the string that will hold the row for the entire
				 * database */
				try {
					statement.clearBatch();
					rs = statement.executeQuery(query);
					rs.next();
				} catch (SQLException e2) {
					System.out.println("\nSynthax error in: \n" + query);
					e2.printStackTrace();
				}
				
				try{	
					/**Get the time stamp first since it is the only different
					 * data type format so far */
					
					/**TODO: A better way of knowing which type to get -
					 * perhaps to start making use of the unit type variable
					 * from the sensor data objects... */
					
					databaseRowPrintOut.append(rs.getTimestamp(1));
					
					/**Get the rest of the values in the buttom row of the
					 * current table */
					for(int j = 2; j < Integer.parseInt(tables.
							getProperty("Table"+(i+1)+"NumOfColumns"))+2; j++){
						databaseRowPrintOut.append(" " + rs.getDouble(j));
					}
					databaseRowPrintOut.append(" | ");
					
				} catch (SQLException e2) {
					System.out.println("\nQuery failed to print: \n" + query);
					e2.printStackTrace();
				} catch (NullPointerException e2) {
					System.out.println("\nQuery failed to print: \n" + query);
				}
				
				
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			
			/**Print out the resulting latest row from the database */
			System.out.println(databaseRowPrintOut.toString());
			
		}
	}
}
